package org.bzjj.servlet.bean.ctrl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.bzjj.log.MyLog;
import org.bzjj.sql.OracleCon;
import org.bzjj.tool.ChatMethod;
import org.bzjj.tool.Tools;

public class GetTj extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String kssj = request.getParameter("kssj").replaceAll("-", "");
		String jssj = request.getParameter("jssj").replaceAll("-", "");
		String name = kssj + "-" + jssj + ".xls";
		String path = this.getServletContext().getRealPath("") + "\\down\\";
		File saveFile=new File(path + name);
		if(saveFile.exists()) {
			ChatMethod.setResponse(response, "{\"code\": 1,\"name\": \"" + name + "\"}");
			return;
		}
		String sql1 = "select case when t1.bmqc is null then '总计' else t1.bmqc end 检测机构,sum(t.amount) \"检验总量(汽车)\",sum(t.zcdjamount) \"注册登记检验数量(汽车)\",sum(t.zycamount) \"在用车检验数量(汽车)\",sum(t.lsamount) \"临时检验数量(汽车)\",sum(t.tsamount) \"特殊检验数量(汽车)\",sum(t.mtamount) \"检验总量(摩托车)\",sum(t.mtzcdjamount) \"注册登记检验数量(摩托车)\",sum(t.mtzycamount) \"在用车检验数量(摩托车)\",sum(t.mtlsamount) \"临时检验数量(摩托车)\",sum(t.mttsamount) \"特殊检验数量(摩托车)\" from VEH_IS_AMOUNTS_STATS t,frm_department t1 where t.statsday>=to_date('"
				+ kssj + "', 'yyyymmdd') and t.statsday<=to_date('" + jssj
				+ "', 'yyyymmdd') and t.jyjgbh=t1.txzqfr group by rollup(t1.bmqc)";
		String sql2 = "select case when t1.bmqc is null then '总计' else t1.bmqc end 检测机构,sum(t.zjamount) 车辆总检数,sum(t.zjhgamount) 总检合格数,round(sum(t.zjhgamount)/sum(t.zjamount)*100,2)||'%' 合格率,sum(t.cjamount) 车辆初检数,sum(t.cjhgamount) 初检合格数,round(sum(t.cjhgamount)/sum(t.cjamount)*100,2)||'%' 合格率,sum(t.fjamount) 车辆复检数,sum(t.fjhgamount) 复检合格数,round(sum(t.fjhgamount)/sum(t.fjamount)*100,2)||'%' 合格率 from VEH_IS_YIELD_STATS t,frm_department t1 where t.statsday>=to_date('"
				+ kssj + "', 'yyyymmdd') and t.statsday<=to_date('" + jssj
				+ "', 'yyyymmdd') and t.jyjgbh=t1.txzqfr group by rollup(t1.bmqc) order by 车辆总检数";
		String sql3 = "select case when t1.bmqc is null then '总计' else t1.bmqc end 检测机构,sum(t.shamount) 车辆审核总数,sum(t.ycshtgamount) 一次审核通过数,round(sum(t.ycshtgamount)/sum(t.shamount)*100,2)||'%' 一次审核通过率,sum(t.ycshbtgamount) 一次审核不通过数,round(sum(t.ycshbtgamount)/sum(t.shamount)*100,2)||'%' 一次审核不通过率 from VEH_IS_SHRYIELD_STATS t,frm_department t1 where t.statsday>=to_date('"
				+ kssj + "', 'yyyymmdd') and t.statsday<=to_date('" + jssj
				+ "', 'yyyymmdd') and t.jyjgbh=t1.txzqfr group by rollup(t1.bmqc)";
		String sql4 = "select case when t1.bmqc is null then '总计' else t1.bmqc end 检测机构,sum(t.amount) 检验总量,sum(t.bxqamount) 本辖区检验数量,sum(t.snamount) 省内检验数量,sum(t.swamount) 省外检验数量 from VEH_IS_ZYCAREADIST_STATS t,frm_department t1 where t.statsday>=to_date('"
				+ kssj + "', 'yyyymmdd') and t.statsday<=to_date('" + jssj
				+ "', 'yyyymmdd') and t.jyjgbh=t1.txzqfr group by rollup(t1.bmqc) order by 检验总量";
		if (Integer.valueOf(kssj) < Integer.valueOf(jssj)) {
			Connection connection = OracleCon.getDBonnect().getConnection();

			if (connection == null) {
				ChatMethod.setResponse(response, "{\"code\": 0,\"info\": \"连接数据查询异常！\"}");
				return;
			}

			File file = new File(path + "tmp.xls");
			HSSFWorkbook workBook = null;

			try {
				workBook = new HSSFWorkbook(new FileInputStream(file));
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}

			if (workBook == null) {
				ChatMethod.setResponse(response, "{\"code\": 0,\"info\": \"创建表格失败！\"}");
				return;
			}
			//查询数据写入到表格
			Tools.baoBiao(connection, sql1, workBook.getSheet("检验业务量"));
			Tools.baoBiao(connection, sql2, workBook.getSheet("检验合格率"));
			Tools.baoBiao(connection, sql3, workBook.getSheet("审核通过率"));
			Tools.baoBiao(connection, sql4, workBook.getSheet("在用车检验区域分布"));
			//保存到表格
			Tools.saveFile(workBook, saveFile);
			//关闭数据库连接
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//关闭工作表
			try {
				workBook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			//日志记录
			HttpSession session = request.getSession();
			String ip=request.getRemoteAddr();
			String user=(String) session.getAttribute("username");
			Logger logger = MyLog.getLog("CarRejectUpdate");
			logger.info("统计:"+user+"--"+ip);
			// 返回json 成功1+文件名
			ChatMethod.setResponse(response, "{\"code\": 1,\"name\": \"" + name + "\"}");
		} else {
			// 错误返回0+失败信息
			ChatMethod.setResponse(response, "{\"code\": 0,\"info\": \"开始时间不能大于结束时间！\"}");
		}
	}
}